import random
import os

import openpyxl
from openpyxl.chart import AreaChart, Reference


def writeExcel(filePath):
    # 创建一个新的空 Workbook 对象
    wb = openpyxl.Workbook()

    sheet = wb.active
    sheet.title = "test"
    print("{}\n".format(sheet.title))

    wb.create_sheet("firstsheet", 0)
    wb.create_sheet(title="secondsheet", index=1)
    wb.create_sheet()
    wb.create_sheet()
    print("{}\n".format(wb.sheetnames))

    # sheet2 = wb["test"]
    # sheet2['A1'] = "hello world !"

    sheet = wb.active
    sheet['A1'] = "hello world"

    '''
    公式
    '''
    for i in range(1, 11):
        sheet['B' + str(i)] = i
        sheet['C' + str(i)] = i + random.randint(1, 10)
    sheet['B11'] = '=sum(B1:B10)'
    sheet['C11'] = '=sum(C1:C10)'

    '''
    设置行高列宽
    行高：0 到 409 之间的整数或浮点值，一点等于 1/72 英寸，默认 12.75。
    列宽：0 到 255 之间的整数或浮点数。这个值表示使用默认字体大小时（11 点），单元格可以显示的字符数，默认 8.43。
    列宽为零或行高为零，将使单元格隐藏
    '''
    sheet.row_dimensions[1].height = 30
    sheet.column_dimensions['A'].width = 20

    '''
    合并单元格&&拆分单元格
    '''
    sheet.merge_cells('D2:G2')
    sheet.merge_cells('D5:G10')
    sheet['D2'] = 'D2-G2'
    sheet['D5'] = '合并单元格D5-G10'
    sheet.unmerge_cells('D2:G2')

    '''
    冻结或解冻窗格
    单元格上边的所有行和左边的所有列都会冻结，但单元格所在的行和列不会冻结
    '''
    sheet.freeze_panes = 'A2'  # 冻结第一行
    sheet.freeze_panes = 'B2'  # 冻结第一列
    # 以下为取消冻结窗格
    sheet.freeze_panes = ''
    sheet.freeze_panes = 'A1'
    sheet.freeze_panes = None
    # 冻结C3上边和左边的行和列
    sheet.freeze_panes = 'C3'

    '''
    图表
    '''
    sheet = wb['secondsheet']
    rows = [
        ['Number', 'Batch 1', 'Batch 2'],
        [1, 30, 25],
        [2, 40, 30],
        [3, 40, 25],
        [4, 50, 30],
        [5, 30, 10],
        [6, 25, 5],
        [7, 50, 10],
    ]

    for row in rows:
        sheet.append(row)

    chart = AreaChart()
    chart.title = "Area Chart"
    chart.style = 13
    chart.x_axis.title = 'Test'
    chart.y_axis.title = 'Percentage'

    # 横坐标取值区域
    cats = Reference(sheet, min_row=2, min_col=1, max_row=8)
    # 数据取值区域
    data = Reference(sheet, min_row=1, min_col=2, max_row=8, max_col=3)
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(cats)

    sheet.add_chart(chart, "A10")

    wb.save(filePath)

    print("{}\n".format(sheet.title))


# 当我们用openpyxl_write.py执行时，内置变量__name__会是__main__，所以会进入if条件，调用指定函数
if __name__ == '__main__':
    writeExcel("../files/openpyxl_write.xlsx")
